﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Microsoft.VisualBasic;
using System.Collections;
using System.Data;
using System.Diagnostics;
using System.Configuration;
using System.Data.SqlClient;

namespace DHQGHN.DAL.Base
{

    public class SqlHelper
    {

        public static void ExecuteQuery(string connString, string cmdText)
        {

            SqlConnection conn = new SqlConnection(connString);
            //Dim trans As SqlTransaction = conn.BeginTransaction("BuilderTransaction")
            SqlConnection objCon = new SqlConnection(connString);
            SqlCommand obj = null;
            string strSQL = null;

            try
            {
                objCon = new SqlConnection(connString);
                objCon.Open();
                obj = objCon.CreateCommand();
                strSQL = cmdText;
                obj.CommandText = strSQL;
                try
                {
                    obj.ExecuteNonQuery();
                }
                catch (Exception ex)
                {
                    throw new Exception("SQL Exception1 " + ex.Message);
                }

                objCon.Close();
                objCon = null;


            }
            catch (SqlException ex)
            {
                throw new Exception("SQL Exception1 " + ex.Message);
            }
            catch (Exception exx)
            {
                throw new Exception("ExecuteNonQuery Function", exx);
                //Add this for finally closing the connection and destroying the command
            }
            finally
            {
                conn.Close();


            }
        }

        public static int ExecuteNonQuery(string connString, CommandType cmdType, string cmdText, SqlParameter[] cmdParms)
        {

            SqlCommand cmd = new SqlCommand();
            SqlConnection conn = new SqlConnection(connString);
            //Dim trans As SqlTransaction = conn.BeginTransaction("BuilderTransaction")
            try
            {
                PrepareCommand(cmd, conn, cmdType, cmdText, cmdParms);
                int val = cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                return val;
            }
            catch (SqlException ex)
            {
                throw new Exception("SQL Exception1 " + ex.Message);
            }
            catch (Exception exx)
            {
                throw new Exception("ExecuteNonQuery Function", exx);
                //Add this for finally closing the connection and destroying the command
            }
            finally
            {
                conn.Close();
                cmd = null;
                cmdParms = null;
            }
        }

        ///// <summary>
        ///// Execute a OleDbCommand (that returns no resultset) against an existing database connection 
        ///// using the provided parameters.
        ///// </summary>
        ///// <remarks>
        ///// e.g.:  
        /////  int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new OleDbParameter("@prodid", 24));
        ///// </remarks>
        ///// <param name="conn">an existing database connection</param>
        ///// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
        ///// <param name="commandText">the stored procedure name or T-SQL command</param>
        ///// <param name="commandParameters">an array of SqlParamters used to execute the command</param>
        ///// <returns>an int representing the number of rows affected by the command</returns>

        public static int ExecuteNonQuery(SqlConnection conn, CommandType cmdType, string cmdText, SqlParameter[] cmdParms)
        {
            SqlCommand cmd = new SqlCommand();
            try
            {
                PrepareCommand(cmd, conn, cmdType, cmdText, cmdParms);
                //SqlParameter parm = null;
                //For Each parm In cmdParms
                //    cmd.Parameters.Add(parm)
                //Next
                int val = cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                return val;

            }
            catch (SqlException ex)
            {
                throw new Exception("SQL Exception ", ex);
            }
            catch (Exception exx)
            {
                throw new Exception("ExecuteNonQuery", exx);
            }
            finally
            {
                cmd = null;
            }
        }

        //	/// <summary>
        ///// Execute a OleDbCommand that returns a resultset against the database specified in the connection string 
        ///// using the provided parameters.
        ///// </summary>
        ///// <remarks>
        //'/// e.g.:  
        /////  SqlDataReader r = ExecuteReader(connString, CommandType.StoredProcedure, "PublishOrders", new OleDbParameter("@prodid", 24));
        ///// </remarks>
        ///// <param name="connectionString">a valid connection string for a OleDbConnection</param>
        ///// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
        ///// <param name="commandText">the stored procedure name or T-SQL command</param>
        ///// <param name="commandParameters">an array of SqlParamters used to execute the command</param>
        ///// <returns>A SqlDataReader containing the results</returns>

        public static SqlDataReader ExecuteReader(SqlConnection conn, CommandType cmdType, string cmdText, SqlParameter[] cmdParms = null)
        {
            SqlCommand cmd = new SqlCommand();
            //Dim conn As OleDbConnection = New OleDbConnection(connString)
            // we use a try/catch here because if the method throws an exception we want to 
            // close the connection throw ex code, because no datareader will exist, hence the 
            // commandBehaviour.CloseConnection will not work
            try
            {
                PrepareCommand(cmd, conn, cmdType, cmdText, cmdParms);
                SqlDataReader rdr = cmd.ExecuteReader();
                //cmd.Parameters.Clear()
                return rdr;

            }
            catch (SqlException ex)
            {
                throw new Exception("SQL Exception ", ex);
            }
            catch (Exception exx)
            {
                throw new Exception("ExecuteReader", exx);
            }
            finally
            {
                cmd = null;
            }
        }

        public static DataTable ExecuteTable(string connString, CommandType cmdType, string cmdText, SqlParameter[] cmdParms = null)
        {

            SqlCommand cmd = new SqlCommand();
            SqlConnection conn = new SqlConnection(connString);
            SqlDataAdapter oDataAdapter = new SqlDataAdapter();
            DataTable oDataTable = new DataTable();
            try
            {
                PrepareCommand(cmd, conn, cmdType, cmdText, cmdParms);
                //cmd.Parameters = cmdParms
                oDataAdapter.SelectCommand = cmd;
                oDataAdapter.Fill(oDataTable);
                cmd.Parameters.Clear();
                return oDataTable;

            }
            catch (SqlException ex)
            {
                throw new Exception("SQL Exception : " + ex.Message, ex);
            }
            catch (Exception exx)
            {
                throw new Exception("ExecuteTable Exception :", exx);
            }
            finally
            {
                conn.Close();
                cmd = null;
                oDataAdapter = null;
            }
        }

        public static DataTable ExecuteTable(SqlConnection oConnection, CommandType cmdType, string cmdText, SqlParameter[] cmdParms = null)
        {

            SqlCommand cmd = new SqlCommand();
            SqlDataAdapter oDataAdapter = new SqlDataAdapter();
            DataTable oDataTable = new DataTable();
            try
            {
                PrepareCommand(cmd, oConnection, cmdType, cmdText, cmdParms);
                oDataAdapter.SelectCommand = cmd;
                oDataAdapter.Fill(oDataTable);
                cmd.Parameters.Clear();
                return oDataTable;
            }
            catch (Exception ex)
            {
                throw new Exception("ExecuteTable", ex);
            }
            finally
            {
                cmd.Connection.Close();
                cmd.Dispose();
                oDataAdapter.Dispose();
                oDataTable.Dispose();
            }
        }

        public static DataSet ExecuteDataSet(string connString, CommandType cmdType, string cmdText, SqlParameter[] cmdParms = null)
        {
            SqlCommand cmd = new SqlCommand();
            SqlConnection conn = new SqlConnection(connString);

            SqlDataAdapter oDataAdapter = new SqlDataAdapter();
            DataSet oDataSet = new DataSet();
            try
            {
                PrepareCommand(cmd, conn, cmdType, cmdText, cmdParms);
                oDataAdapter.SelectCommand = cmd;
                //cmd.Connection = conn
                oDataAdapter.Fill(oDataSet);
                cmd.Parameters.Clear();
                return oDataSet;

            }
            catch (SqlException ex)
            {
                throw new Exception("SQL Exception ", ex);
            }
            catch (Exception exx)
            {
                throw new Exception("ExecuteDataSet", exx);
            }
            finally
            {
                conn.Close();
                cmd = null;
                oDataAdapter = null;
            }
        }

        public static DataRow ExecuteRow(string connString, CommandType cmdType, string cmdText, SqlParameter[] cmdParms = null)
        {
            SqlCommand cmd = new SqlCommand();
            SqlConnection conn = new SqlConnection(connString);
            SqlDataAdapter oDataAdapter = new SqlDataAdapter();
            //DataRow oDataRow = null;
            DataTable oDataTable = new DataTable();
            try
            {
                PrepareCommand(cmd, conn, cmdType, cmdText, cmdParms);
                oDataAdapter.SelectCommand = cmd;
                oDataAdapter.Fill(oDataTable);
                cmd.Parameters.Clear();
                if (oDataTable.Rows.Count == 0)
                {
                    return null;
                }
                else
                {
                    DataRow oRow = oDataTable.Rows[0];
                    return oRow;
                }

            }
            catch (SqlException ex)
            {
                throw new Exception("SQL Exception ", ex);
            }
            catch (Exception exx)
            {
                throw new Exception("ExecuteRow", exx);
            }
            finally
            {
                conn.Close();
                oDataTable = null;
                cmd = null;
                oDataAdapter = null;
            }
        }


        public static DataRow ExecuteRow(ref SqlConnection oConnection, CommandType cmdType, string cmdText, SqlParameter[] cmdParms = null)
        {
            SqlCommand cmd = new SqlCommand();
            SqlConnection conn = oConnection;
            SqlDataAdapter oDataAdapter = new SqlDataAdapter();
            //DataRow oDataRow = null;
            DataTable oDataTable = new DataTable();
            try
            {
                PrepareCommand(cmd, conn, cmdType, cmdText, cmdParms);
                oDataAdapter.SelectCommand = cmd;
                oDataAdapter.Fill(oDataTable);
                cmd.Parameters.Clear();
                if (oDataTable.Rows.Count == 0)
                {
                    return null;
                }
                else
                {
                    DataRow oRow = oDataTable.Rows[0];
                    return oRow;
                }

            }
            catch (SqlException ex)
            {
                throw new Exception("SQL Exception ", ex);
            }
            catch (Exception exx)
            {
                throw new Exception("ExeculateScalar", exx);
            }
            finally
            {
                oDataTable = null;
                cmd.Connection.Close();
                cmd = null;
                oDataAdapter = null;
            }
        }

        ///// <summary>
        ///// Execute a OleDbCommand that returns the first column of the first record against the database specified in the connection string 
        ///// using the provided parameters.
        ///// </summary>
        ///// <remarks>
        ///// e.g.:  
        /////  Object obj = ExecuteScalar(connString, CommandType.StoredProcedure, "PublishOrders", new OleDbParameter("@prodid", 24));
        ///// </remarks>
        ///// <param name="connectionString">a valid connection string for a OleDbConnection</param>
        ///// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
        ///// <param name="commandText">the stored procedure name or T-SQL command</param>
        ///// <param name="commandParameters">an array of SqlParamters used to execute the command</param>
        ///// <returns>An object that should be converted to the expected type using Convert.To{Type}</returns>

        public static object ExecuteScalar(string connString, CommandType cmdType, string cmdText, SqlParameter[] cmdParms)
        {
            SqlCommand cmd = new SqlCommand();
            SqlConnection conn = new SqlConnection(connString);
            try
            {
                PrepareCommand(cmd, conn, cmdType, cmdText, cmdParms);
                object val = cmd.ExecuteScalar();

                cmd.Parameters.Clear();
                return val;

            }
            catch (SqlException ex)
            {
                throw new Exception("SQL Exception ", ex);
            }
            catch (Exception exx)
            {
                throw new Exception("ExeculateScalar", exx);
            }
            finally
            {
                conn.Close();
                conn = null;
                cmd = null;
            }
        }


        ///// <summary>
        ///// Execute a OleDbCommand that returns the first column of the first record against an existing database connection 
        ///// using the provided parameters.
        ///// </summary>
        ///// <remarks>
        ///// e.g.:  
        /////  Object obj = ExecuteScalar(connString, CommandType.StoredProcedure, "PublishOrders", new OleDbParameter("@prodid", 24));
        ///// </remarks>
        ///// <param name="conn">an existing database connection</param>
        ///// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
        ///// <param name="commandText">the stored procedure name or T-SQL command</param>
        ///// <param name="commandParameters">an array of SqlParamters used to execute the command</param>
        ///// <returns>An object that should be converted to the expected type using Convert.To{Type}</returns>

        public static object ExecuteScalar(ref SqlConnection conn, CommandType cmdType, string cmdText, SqlParameter[] cmdParms)
        {

            SqlCommand cmd = new SqlCommand();
            try
            {
                PrepareCommand(cmd, conn, cmdType, cmdText, cmdParms);
                object val = cmd.ExecuteScalar();
                cmd.Parameters.Clear();
                return val;
            }
            catch (SqlException ex)
            {
                throw new Exception("SQL Exception ", ex);
            }
            catch (Exception exx)
            {
                throw new Exception("ExeculateScalar", exx);
            }
            finally
            {
                cmd.Connection.Close();
                cmd = null;
            }
        }

        ///// <summary>
        ///// add parameter array to the cache
        ///// </summary>
        ///// <param name="cacheKey">Key to the parameter cache</param>
        ///// <param name="cmdParms">an array of SqlParamters to be cached</param>

        //Public Function CacheParameters(ByVal cacheKey As String, ByVal cmdParms As OleDbParameter())
        //    parmCache(cacheKey) = cmdParms
        //End Function

        ///// <summary>
        ///// Retrieve cached parameters
        ///// </summary>
        ///// <param name="cacheKey">key used to lookup parameters</param>
        ///// <returns>Cached SqlParamters array</returns>

        //Public Function GetCachedParameters(ByVal cacheKey As String) As OleDbParameter()
        //    Dim cachedParms As OleDbParameter() = parmCache(cacheKey)
        //    If IsNothing(cachedParms) Then
        //        Return Nothing
        //    End If

        //    Dim clonedParms() As OleDbParameter = New OleDbParameter("abc", cachedParms.Length)
        //    Dim i As Integer
        //    Dim j As Integer = cachedParms.Length
        //    For i = 0 To j < 1
        //		clonedParms(i) = (OleDbParameter)((ICloneable)cachedParms(i)).Clone();
        //        Return clonedParms
        //    Next


        ///// <summary>
        ///// Prepare a command for execution
        ///// </summary>
        ///// <param name="cmd">OleDbCommand object</param>
        ///// <param name="conn">OleDbConnection object</param>
        ///// <param name="trans">SqlTransaction object</param>
        ///// <param name="cmdType">Cmd type e.g. stored procedure or text</param>
        ///// <param name="cmdText">Command text, e.g. Select * from Products</param>
        ///// <param name="cmdParms">OleDbParameters to use in the command</param>

        public static bool PrepareCommand(SqlCommand cmd, SqlConnection conn, CommandType cmdType, string cmdText, SqlParameter[] cmdParms)
        {
            if (!(conn.State == ConnectionState.Open))
            {
                //MsgBox("Connection open")
                conn.Open();
            }
            try
            {
                cmd.Connection = conn;
                cmd.CommandText = cmdText;
                cmd.Parameters.Clear();
                //  cmd.ParameterCheck = True
                cmd.CommandType = cmdType;
                if (!((cmdParms == null)))
                {
                    SqlParameter parm = null;
                    foreach (SqlParameter parm_loopVariable in cmdParms)
                    {
                        parm = parm_loopVariable;
                        cmd.Parameters.Add(parm);
                    }
                }

            }
            catch (SqlException ex)
            {
                throw new Exception("SQL Exception ", ex);
            }
            catch (Exception exx)
            {
                throw new Exception("PrepareCommand : ", exx);
            }
            return true;
        }

        public static bool PrepareCommand11(ref SqlCommand cmd, ref SqlConnection conn, ref CommandType cmdType, ref string cmdText, ref SqlParameter[] cmdParms)
        {


            cmd.Connection = conn;
            cmd.CommandText = cmdText;
            cmd.Parameters.Clear();
            //  cmd.ParameterCheck = True
            cmd.CommandType = cmdType;

            return true;
        }

        //public static bool ExcuteAdapter(string connString, DataTable oTable, string cmdText, ref long lngMaxID )
        //{

        //    SqlConnection conn = null;
        //    SqlDataAdapter oDataAdapter = new SqlDataAdapter();
        //    SqlCommand oSqlCmd = new SqlCommand();
        //    SqlCommandBuilder oCmdBuilder = null;
        //    try
        //    {
        //        conn = new SqlConnection(connString);
        //        if (!(conn.State == ConnectionState.Open))
        //        {
        //            conn.Open();
        //        }

        //        oSqlCmd.Connection = conn;
        //        oSqlCmd.CommandText = cmdText;
        //        oSqlCmd.CommandType = CommandType.Text;
        //        oDataAdapter.SelectCommand = oSqlCmd;
        //        oCmdBuilder = new SqlCommandBuilder(oDataAdapter);
        //        oCmdBuilder.GetUpdateCommand();
        //        oCmdBuilder.GetInsertCommand();
        //        oCmdBuilder.GetDeleteCommand();
        //        oDataAdapter.Update(oTable);
        //        oDataAdapter.SelectCommand = new SqlCommand("SELECT @@IDENTITY", conn);
        //        lngMaxID = Convert.ToInt64(oDataAdapter.SelectCommand.ExecuteScalar());

        //    }
        //    catch (SqlException ex)
        //    {
        //        throw new Exception("SQL Exception ", ex);
        //    }
        //    catch (Exception exx)
        //    {
        //        throw new Exception("ExeculateAdapter", exx);
        //    }
        //    finally
        //    {
        //        // cmd.Connection.Close()
        //        if (conn.State == ConnectionState.Open)
        //            conn.Close();
        //        oSqlCmd = null;
        //        oDataAdapter = null;
        //        oCmdBuilder = null;

        //    }

        //}
        public static DataTable GetDataTable(string sConnectionString, string pstrSQL)
        {
            SqlCommand cmd = new SqlCommand();
            SqlConnection SQLconn = new SqlConnection(sConnectionString);
            DataTable rsData = new DataTable();
            SqlDataAdapter adpAdapter = new SqlDataAdapter();

            try
            {
                SQLconn.Open();
                cmd.Connection = SQLconn;
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = pstrSQL;
                cmd.CommandTimeout = 30000;

                adpAdapter.SelectCommand = cmd;
                adpAdapter.Fill(rsData);

                cmd.Dispose();
                SQLconn.Close();
                SQLconn.Dispose();
                adpAdapter.Dispose();
                return rsData;
            }
            catch (Exception exp)
            {
                cmd.Dispose();
                SQLconn.Close();
                SQLconn.Dispose();
                adpAdapter.Dispose();
                string strErr = exp.Message;
                return null;
            }
        }
        /// <summary>
        /// Params: string pstrSQl
        /// Purpose: get Records from SQL string to Datatable
        /// </summary>
        /// 
        public static DataSet GetDataSet(string sConnectionString, string pstrSQL)
        {
            SqlCommand cmd = new SqlCommand();
            SqlConnection SQLconn = new SqlConnection(sConnectionString);

            DataSet rsData = new DataSet();
            SqlDataAdapter adpAdapter = new SqlDataAdapter();

            try
            {
                SQLconn.Open();
                cmd.Connection = SQLconn;
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = pstrSQL;
                cmd.CommandTimeout = 30000;

                adpAdapter.SelectCommand = cmd;
                adpAdapter.Fill(rsData);

                cmd.Dispose();
                SQLconn.Close();
                SQLconn.Dispose();
                adpAdapter.Dispose();
                return rsData;
            }
            catch (Exception exp)
            {
                cmd.Dispose();
                SQLconn.Close();
                SQLconn.Dispose();
                adpAdapter.Dispose();
                string strErr = exp.Message;
                return null;
            }
        }
    }
}

